import DatabaseUtils from "../utils/db-utils";
import {BOOLEAN} from "../configs/config";

class UserDao {

    /**
     * 查询用户信息
     * @param where
     * @returns
     */
    static queryUserTable = async (where: any[]) => {
        const tableName = 't_user';
        const columns = ['user_id', 'user_name', 'password', 'icon_url', 'email', 'user_type', 'score', 'pk_total_count', 'pk_win_count',
            'pk_fail_count', 'pk_peace_count', 'pk_offline_count', 'ip', 'finger', 'ticket', 'data_status', 'create_time', 'update_time'];
        const sqlObj = DatabaseUtils.getDynamicSelectObj(tableName, columns, where);
        return await DatabaseUtils.execSql(sqlObj.sql, sqlObj.params);
    };

    /**
     * 动态更新用户表
     * @param columns
     * @param where
     */
    static updateUserTable = async (columns: any[], where: any[]) => {
        const tableName = 't_user';
        const sqlObj = DatabaseUtils.getDynamicUpdateObj(tableName, columns, where);
        return await DatabaseUtils.execSql(sqlObj.sql, sqlObj.params);
    };

    /**
     * 检查房间内的用户类型是否与当前用户相同
     * @param userId
     * @param appointRoomId
     */
    static checkUserTypeIsIdentical = async (userId: string, appointRoomId: string) => {
        if(appointRoomId) {
            const sql = `select user_type as userType from t_user a left join t_user_state b on a.user_id = b.user_id
                    where b.room_id = ? or a.user_id = ?`;
            const params = [appointRoomId, userId];
            const rows: any = await DatabaseUtils.execSql(sql, params);
            if(rows.length <= 1) {
                // 仅用户本身，不参与比较
                return true;
            } else {
                const [one, two] = rows;
                return one.userType === two.userType;
            }
        }
        return true;
    }

    /**
     * 创建用户
     * @param userId
     * @param password
     * @param userName
     * @param email
     * @param userType
     * @param ticket
     * @param finger
     * @param ipAddress
     */
    static createUser = async (userId: string, password: string, userName: string, email: string | null,
                               userType: string, ticket: string | null, finger: string, ipAddress: string) => {
        // 注册用户信息
        const sql = `insert into t_user(user_id,password,email,user_type,score,pk_total_count,pk_win_count, 
                            pk_offline_count, pk_fail_count, pk_peace_count, finger, ip, ticket, user_name, data_status) 
                     values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`;
        const params = [userId, password, email, userType, 1000, 0, 0, 0, 0, 0, finger, ipAddress, ticket, userName, BOOLEAN.SHORT_YES];
        await DatabaseUtils.execSql(sql, params);
        return true;
    };

    /**
     * 根据用户Id获取用户详情
     * @param userId
     */
    static getUserByPrimaryKey = async (userId: string) => {
        const sql = `select user_id as userId, user_name as userName, password, email, user_type as userType, icon_url as iconUrl,
                        score, pk_total_count as pkTotalCount, pk_win_count as pkWinCount, pk_fail_count as pkFailCount,
                        pk_peace_count as pkPeaceCount, pk_offline_count as pkOfflineCount, ip, finger, ticket, data_status
                      from t_user where user_id = ?`;
        const params = [userId];
        const rows: any = await DatabaseUtils.execSql(sql, params);
        // 有且只有一条数据
        return rows.length === 0 ? null : rows[0];
    }

    /**
     * 更新用户数据
     * @param userId
     * @param score
     * @param pkTotalCount
     * @param pkWinCount
     * @param pkOfflineCount
     * @param pkFailCount
     * @param pkPeaceCount
     * @return {Promise<boolean>}
     */
    static updateUser = async (userId: string, score: number, pkTotalCount: number, pkWinCount: number,
                        pkOfflineCount: number, pkFailCount: number, pkPeaceCount: number) => {
        const sql = 'update t_user set score = ?, pk_total_count = ?, pk_win_count = ?, pk_offline_count = ?, pk_fail_count = ?, pk_peace_count = ? where user_id = ?';
        const params: any[] = [score, pkTotalCount, pkWinCount, pkOfflineCount, pkFailCount, pkPeaceCount, userId];
        await DatabaseUtils.execSql(sql, params);
        return true;
    };

    /**
     * 修改用户密码
     * @param userId
     * @param newPassword
     * @return {Promise<boolean>}
     */
    static updateUserPassword = async (userId: string, newPassword: string) => {
        const sql = `update t_user set password = ? where user_id = ?`;
        const params = [newPassword, userId];
        const rows: any = await DatabaseUtils.execSql(sql, params);
        return rows.length === 1;
    }

    /**
     * 根据userId查询排名
     * @param userId
     */
    static getScoreSortByUserId = async (userId: string) => {
        const sql = ` select s.scoreSort as total from t_user a
            left join (SELECT @rowNum := @rowNum + 1 AS scoreSort, u.user_id as userId
                         FROM t_user u, (SELECT @rowNum := 0) as r
                        where u.pk_total_count > 0 order by u.score desc, u.pk_win_count desc) s on a.user_id = s.userId
            where a.user_id = ?`;
        const params = [userId];
        const rows: any = await DatabaseUtils.execSql(sql, params);
        return rows.length > 0 ? rows[0].total : null;
    }

    /**
     * 检查IP是否在短时间内重复注册
     * @param ipAddress
     * @param finger
     * @param userType
     * @param timeLimitHours
     */
    static checkShortTimeRepeatRegister = async (ipAddress: string, finger: string, userType: string, timeLimitHours: number) => {
        const sql = `select * from t_user where (ip = ? or finger = ?) and user_type = ? and TIMESTAMPDIFF(hour, create_time, now()) <= ?`;
        const params = [ipAddress, finger, userType, timeLimitHours];
        const rows: any = await DatabaseUtils.execSql(sql, params);
        return rows.length > 0;
    }
}

export default UserDao;